﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule.MasterData
{
    public class PersonManager : BusinessLogic
    {
     
        public Person Load(decimal id)
        {
            return (Person)ObjectRow.Load(typeof(Person), string.Format("this.ID='{0}'", id));
        }
        public Person LoadByUserID(decimal id)
        {
            return (Person)ObjectRow.Load(typeof(Person), string.Format("this.ID in ( select PERSON_ID from Users where ID={0})", id));
        }

        public decimal Save(Person person)
        {
            if (person != null)
            {
                try
                {
                    DataRowState state = person.State;
                    person.LAST_UPDATE_TIME = BusinessLogic.GetDateTimeDecimal(DateTime.Now);
                    person.Save();

                    //获取员工号
                    //if (state == DataRowState.Added)
                    //{
                    //    object obj = SqlData.ExecuteScalar(string.Format("select id from persons where code='{0}' and valid='1'", person.Code));
                    //    if (obj != null && obj != DBNull.Value)
                    //    {
                    //        person.ID = decimal.Parse(obj.ToString());
                    //    }
                    //}
                }
                catch (Exception err)
                {
                    message = err.Message; 
                    throw err;
                }
            }
            return person.ID;
        }


     

        /// <summary>
        /// 返回人员信息
        /// </summary>
        /// <param name=0></param>
        /// <returns></returns>
        public DataTable GetPersonByDomain(decimal DomainID,decimal orgID, decimal PersonID, bool? IsUser,Byte type)
        {
            string sql = string.Format(@"with OrganizationTree(NAME,Id,parentid) as
                    ( 
	                    select NAME,Id,parentid from ORGANIZATION where id={0} and domain_id={4}
		                    union all
	                    select t2.name,t2.id,t2.parentid from ORGANIZATION t2
	                    inner join OrganizationTree t1  on t1.id =t2.parentid
	                    where t2.active='1' and t2.domain_id={4}
                    ) select PERSONS.ID,PERSONS.NAME from PERSONS inner join OrganizationTree on PERSONS.ORG_ID=OrganizationTree.id
                where PERSONS.active='1'and type='{3}' and Persons.ID != {1} {2} 
                order by name", orgID, PersonID, IsUser == null ? "" : "and Persons.ID " + (IsUser.Value ? " in (select Person_Id from Users)" : " not in  (select Person_Id from Users) "), type, DomainID);

            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }
        
        public DataTable GetPersonByThridPartyCom(decimal Domain_ID,int thridPartyComId)
        {
            string sql = String.Format("select P.ID,P.NAME from persons P where P.Type=2 and Domain_ID={0} and not exists (select 1 from Users where p.ID=users.PERSON_ID) ", Domain_ID);
            if (thridPartyComId != 0)
            {
                sql = sql + "  and P.org_id  = " + thridPartyComId;
            }
            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public static DataSet SelectPERSONSsDynamic(String name,
                                                    String sex,
                                                    String position,
                                                    decimal domainID,
                                                    String orgID,
                                                    String valid, Byte type)
        {
            return Jumpton.Data.SqlData.OpenSP("sp_SelectPERSONSsDynamic", name,
                                                sex,
                                                position,
                                                domainID,
                                                orgID,
                                                valid);
        }

        public static DataSet SelectPERSONSsDynamicForExcel(string name,
                                                    string sex,
                                                    string position,
                                                    decimal domainID,
                                                    string orgID,
                                                    string valid)
        {
            return Jumpton.Data.SqlData.OpenSP("sp_SelectPERSONSsDynamicForExcel", name,
                                                sex,
                                                position,
                                                domainID,
                                                orgID,
                                                valid);
        }


        /// <summary>
        /// 获取取没有创建用户的人员信息
        /// </summary>
        /// <param name="domainID"></param>
        /// <returns></returns>
        public DataTable GetPersonWithNoUser(string domainID, Byte type)
        {
            string sql = @"select ID,NAME from PERSONS 
                            where ID not in (select PERSON_ID from dbo.USERS) ";//AND ID NOT IN (SELECT PERSON_ID FROM CUSTOMER_CONTACTS)";

            if (!string.IsNullOrEmpty(domainID))
                sql += " and DOMAIN_ID='" + domainID + "'";
            sql += " and type=" + type;
            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];

        }

        /// <summary>
        /// 查找该员工编号是否已经被使用
        /// </summary>
        /// <param name="DomainID"></param>
        /// <param name="UserID"></param>
        /// <param name="IncludeDisable">是否包含无效记录里面的Code</param>
        /// <returns></returns>
        public bool ExistCode(decimal DomainID, String  Code, bool IncludeDisable)
        {
            string Condition = string.Format("select count(*) from Persons where Domain_ID={0} and CODE='{1}' {2}",
                DomainID, Code, IncludeDisable ? "" : " and active = '1'");
            var value =SqlData.ExecuteScalar(Condition);
            int i = 0;
            return !i.Equals(value);
        }

       

        //获取某一部门的人员
        public ObjectTable LoadByOrganizationID(decimal OrganizationID, bool IncludeDisable, Byte type)
        {
            return new ObjectTable(typeof(Person), string.Format("ORG_ID='{0}' {1} and type={2}", OrganizationID, IncludeDisable ? "" : " and active='1'", type));
        }

        //获取某一部门的人员不包含自己
        public ObjectTable LoadByOrganizationID(decimal OrganizationID, bool IncludeDisable, string selfid, Byte type )
        {
            return new ObjectTable(typeof(Person), string.Format("ORG_ID='{0}' AND ID NOT IN ({1}) {2} and type={3}", OrganizationID, selfid, IncludeDisable ? "" : " and active='1'", type));
        }

       
        //获取某一部门自己的下属包含自己
        public ObjectTable LoadPersons(decimal OrganizationID, bool IncludeDisable, string selfid,Byte type)
        {
            return new ObjectTable(typeof(Person), string.Format("ORG_ID='{0}' AND (MANAGER_ID={1} OR ID IN ({2}))  {3} and type={4}", OrganizationID, selfid, selfid, IncludeDisable ? "" : " and active='1'", type));
        }




        public bool IsManager(decimal PERSONID, decimal DomainID)
        {
            string Condition = string.Format("select count(ID) from Persons where Domain_ID={0} and MANAGER_ID='{1}' ",
                DomainID,PERSONID);
            var value = SqlData.ExecuteScalar(Condition);
            int i = 0;
            return !i.Equals(value);
        }


    }
}
